package org.example;

import org.example.bean.TeamBean;
import org.example.util.DBUtil;


import java.sql.*;

public class Main {
    public static void main(String[] args) {


        try (Connection connection = DBUtil.getConnection();
             Statement st = connection.createStatement()) {
            String sql = "SELECT c.pk_groupid, c.group_name, a.team_name team1,b.team_name team2 FROM (" +
                    "(SELECT pk_groupid,group_name FROM t_group)c " +
                    "JOIN " +
                    "(SELECT pk_groupid, team_name FROM t_group g left join t_team t on g.fk_teamid_1 = t.pk_teamid)a " +
                    "JOIN " +
                    "(SELECT pk_groupid, team_name FROM t_group g2 LEFT JOIN t_team t2 on g2.fk_teamid_2 = pk_teamid)b)" +
                    "WHERE (a.pk_groupid = b.pk_groupid)AND(a.pk_groupid = c.pk_groupid);";
            ResultSet rs = st.executeQuery(sql);
            int num = 0;
            while (rs.next()) {
                int id = rs.getInt("c.pk_groupid");
                String name = rs.getString("c.group_name");
                String team1 = rs.getString("team1");
                String team2 = rs.getString("team2");
                System.out.println(id + "\t" + name + "\t" + team1 + "\t" + team2 + "\t");
                num++;
            }
            System.out.println("数据" + num + "条");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }
}